  <?php
 defined('_JEXEC') or die('Restricted access');
/**
* @version		$Id:cashbag.php  1 2013-10-12 13:59:21Z  $
* @package		Cashbag
* @subpackage 	Models
* @copyright	Copyright (C) 2013, . All rights reserved.
* @license #
*/
 defined('_JEXEC') or die('Restricted access');
/**
 * CashbagModelCashbag 
 * @author 
 */
 
 
class CashbagModelCashbag  extends CashbagModel { 

	
	
	protected $_default_filter  = 'cb.demandedOn';   
        var $prix_total             = 0;

/**
 * Constructor
 */
	
	public function __construct()
	{
		parent::__construct();

	}

	/**
	* Method to build the query
	*
	* @access private
	* @return string query	
	*/
        public function getCashFilters(){
            $filters    = array();
            
            $filters[]      = $this->getTypeF();
            
            return $filters;
        }
        
        public function getTypeF(){
            
        $db= JFactory::getDbo();
    	$query= $db->getQuery(true);
    	$query->select('DISTINCT type_cashbag AS type');
    	$query->from($db->quoteName('#__vm_cashbag'));
    	$query->where($db->quoteName('type_cashbag') . ' <> "null"');
        $query->order('type_cashbag ASC');
		 
        // Reset the query using our newly populated query object.
        $db->setQuery($query);

        // Load the results as a list of stdClass objects (see later for more options on retrieving data).
        $types = $db->loadObjectList();


        if (count($types)>1) {// s'il y a plus d'un context, alors alors le filtre sera present!

                $options = array();
                $options[] = JHTML::_('select.option', '','S&eacute;lectionnez');
                foreach ($types AS $ct) {
                        $options[] = JHTML::_('select.option', $ct->type, $ct->type);
                }

                $html	= '<label>Type de demande (Bag ou label)</label>';
                $html	.= JHTML::_('select.genericlist', $options, 'type', '', 'value', 'text', $this->type());

        }
        else $html = '<input type="hidden" name="type" value=""/>';

        return $html;
        }
        
        function type(){
            return JFactory::getApplication()->input->get('type');
        }
	protected function _buildQuery()
	{
	
            $app    = & JFactory::getApplication();
            
            $demanded   = $app->input->get("demandedOn");
            $receveid   = $app->input->get("receivedOn");
            $nom        = $app->input->get("nom_client_bag");
            $type       = $app->input->get('type');
            //$published  = $app->input->get("published");//
            
            $select='SELECT cb.id AS idb,cb.vm_user_id AS id, count(vmp.cashbag_id) AS nbre_bag, cb.demandedOn AS demande, cb.ref_interne AS ref_interne, cb.sendOn AS envoie, cb.receivedOn AS reception,
                                cb.number_of_product_cash AS nbre_prod, cb.type_cashbag AS type, cb.bag_ref AS bag_ref,cb.published, 
                                cb.cashbag_estimation AS estimation, vu.title AS titre, vu.last_name AS nom, vu.first_name AS prenom,
                                vu.address_1 AS adresse, vu.city AS ville, vu.zip AS code_postale
                     FROM #__vm_cashbag AS cb
                     JOIN #__virtuemart_userinfos AS vu ON cb.vm_user_id = vu.virtuemart_user_id
                     JOIN #__users AS u ON (cb.vm_user_id = u.id )
                     JOIN #__virtuemart_products AS vmp ON (cb.id = vmp.cashbag_id)';
            
            $select='SELECT cb.id AS idb,cb.vm_user_id AS id, cb.demandedOn AS demande, cb.ref_interne AS ref_interne, cb.sendOn AS envoie, cb.receivedOn AS reception,
                                cb.number_of_product_cash AS nbre_prod, cb.type_cashbag AS type, cb.bag_ref AS bag_ref,cb.published, 
                                cb.cashbag_estimation AS estimation, vu.title AS titre, vu.last_name AS nom, vu.first_name AS prenom,
                                vu.address_1 AS adresse, vu.city AS ville, vu.zip AS code_postale
                     FROM #__vm_cashbag AS cb
                     JOIN #__virtuemart_userinfos AS vu ON cb.vm_user_id = vu.virtuemart_user_id
                     JOIN #__users AS u ON (cb.vm_user_id = u.id )
                     LEFT JOIN #__virtuemart_products AS vmp ON (cb.id = vmp.cashbag_id)';
            
            /*$select='SELECT cb.id AS idb,cb.vm_user_id AS id, count(vmp.cashbag_id) AS nbre_bag, cb.demandedOn AS demande, cb.ref_interne AS ref_interne, cb.sendOn AS envoie, cb.receivedOn AS reception,
                                count(vmp.cashbag_id) AS nbre_prod, cb.type_cashbag AS type, cb.bag_ref AS bag_ref,cb.published, 
                                cb.cashbag_estimation AS estimation, vu.title AS titre, vu.last_name AS nom, vu.first_name AS prenom,
                                vu.address_1 AS adresse, vu.city AS ville, vu.zip AS code_postale
                     FROM cash_vm_cashbag AS cb
                     JOIN cash_virtuemart_userinfos AS vu ON cb.vm_user_id = vu.virtuemart_user_id
                     JOIN cash_users AS u ON (cb.vm_user_id = u.id )
                     JOIN cash_virtuemart_products AS vmp ON (cb.id = vmp.cashbag_id)';*/
                
              $wheres = array();
              //$wheres[] = ' vmp.cashbag_id <> 0';
              if($demanded!="") $wheres[]= " (cb.demandedOn BETWEEN '".$demanded." 00:00:00' AND '".$demanded." 23:59:59') ";
              if($receveid!="") $wheres[]= " (cb.receivedOn BETWEEN '".$receveid." 00:00:00' AND '".$receveid." 23:59:59') ";
              if($nom!= "")   $wheres[]= " vu.last_name LIKE '".$nom."' ";
              if($type!= "")   $wheres[]= " (cb.type_cashbag LIKE '".$type."' )";
              //if($published!= "")   $wheres[]= "cb.published = $published";
              
              if(count($wheres)>1 ) $where= ' WHERE '.implode(' AND ', $wheres);
              elseif(count($wheres)==1) $where= ' WHERE '.$wheres[0];
              else $where = ' WHERE 1';
              
              $group  = ' Group by cb.id DESC ';
              
              $query    = $select.' '.$where.' '.$group;
              //var_dump($query);
              return $query;
        }
        
        public function addRef(){//envoie d'un bag!
            
            $app    = & JFactory::getApplication();
           
            $data= new stdClass();
            $data->id           = $app->input->get('bag_id');
                        
            $date               = JFactory::getDate();
            $data->sendOn       = $date->toFormat();
            $data->published    = 2;
            return JFactory::getDbo()->updateObject('#__vm_cashbag', $data, 'id');
                
        }
        
	/**
	 * Method to store the Item
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	public function store($data)
	{
		$row =& $this->getTable();
		/**
		 * Example: get text from editor 
		 * $Text  = JRequest::getVar( 'text', '', 'post', 'string', JREQUEST_ALLOWRAW );
		 */
		 
		// Bind the form fields to the table
		if (!$row->bind($data)) {
			$this->setError($row->getError());
			return false;
		}

		// Make sure the table is valid
		if (!$row->check()) {
			$this->setError($row->getError());
			return false;
		}
		
		/**
		 * Clean text for xhtml transitional compliance
		 * $row->text		= str_replace( '<br>', '<br />', $Text );
		 */
	
		// Store the table to the database
		if (!$row->store()) {
			$this->setError($row->getError());
			return false;
		}
		$this->setId($row->{$row->getKeyName()});
		return $row->{$row->getKeyName()};
	}	

        
        function db_bagdetails(){
             $db		= &JFactory::getDbo();
            
            $bag_id     = JFactory::getApplication()->input->get('bag_id');
            
            $query  ="SELECT p.product_type, p.product_state, p.product_cash_price,p.product_cash_comment AS comment, mf.mf_name, cb.vm_user_id, cb.ref_interne "
                    ." FROM #__virtuemart_products AS p"
                    ." JOIN #__virtuemart_product_manufacturers AS m ON p.virtuemart_product_id = m.virtuemart_product_id"
                    ." JOIN #__virtuemart_manufacturers_fr_fr AS mf ON m.virtuemart_manufacturer_id = mf.virtuemart_manufacturer_id"
                    . " JOIN #__vm_cashbag AS cb ON p.cashbag_id = cb.id "
                    . " WHERE p.cashbag_id = $bag_id";
            
            $db->setQuery($query);
            
            return $db->loadObjectList();//on retourne le résultat!
        }
        
        function fnEncrypt($sValue, $sSecretKey)
        {
            return rtrim(
                base64_encode(
                    mcrypt_encrypt(
                        MCRYPT_RIJNDAEL_256,
                        $sSecretKey, $sValue, 
                        MCRYPT_MODE_ECB, 
                        mcrypt_create_iv(
                            mcrypt_get_iv_size(
                                MCRYPT_RIJNDAEL_256, 
                                MCRYPT_MODE_ECB
                            ), 
                            MCRYPT_RAND)
                        )
                    ), "\0"
                );
        }

        
        
         public function SendMail(){
            
             $db		= &JFactory::getDbo();
            
            $bag_id     = JFactory::getApplication()->input->get('bag_id');
            
            $query  ="SELECT p.product_type, p.product_state, p.product_cash_price, p.product_cash_comment AS comment, mf.mf_name, cb.vm_user_id, cb.ref_interne, cb.published "
                    ." FROM #__virtuemart_products AS p"
                    ." JOIN #__virtuemart_product_manufacturers AS m ON p.virtuemart_product_id = m.virtuemart_product_id"
                    ." JOIN #__virtuemart_manufacturers_fr_fr AS mf ON m.virtuemart_manufacturer_id = mf.virtuemart_manufacturer_id"
                    . " JOIN #__vm_cashbag AS cb ON p.cashbag_id = cb.id "
                    . " WHERE p.cashbag_id = $bag_id";
            
            $db->setQuery($query);
            
           // var_dump($query);die();
            
           $details_bag = $db->loadObjectList();//on retourne le résultat!
           
          if($details_bag[0]->published == 2)
              return 'Une proposition a déjà été envoyée pour ce CASHBAG';
          
          if($details_bag[0]->published == 3)
              return 'Le client a déjà accepté une proposition sur ce CASHBAG';
          
           if($details_bag[0]->published == 4)
              return 'Le client a déjà refusé une proposition sur ce CASHBAG';
           
         $id_user     = $details_bag[0]->vm_user_id;
        // var_dump($id_user);
           $user =& JFactory::getUser($id_user);
            //$session =& JFactory::getSession();
           
        $nom = $user->name;
        $mail = $user->email;
       
        $db = &JFactory::getDbo();
        
        $query  = "SELECT first_name FROM #__virtuemart_userinfos WHERE virtuemart_user_id = $id_user";
        $db->setQuery($query);
        $r = $db->loadAssoc();
        
        $prenom = ucfirst($r['first_name']);
        
        $logo   = '<img src="'.JPATH_COMPONENT_ADMINISTRATOR.DS.'models'.DS.'letters'.DS.'logo.jpg">';
        $content    = file_get_contents(JPATH_COMPONENT_ADMINISTRATOR.DS.'models'.DS.'letters'.DS.'proposition.html');
        
        $content = utf8_encode($content);
       // var_dump($content);die();
        $tableau    = $this->bagdetails();
        
        $message    = str_replace('{tableau}', $tableau, $content);
        
        $message    = str_replace('{total}', $this->prix_total, $message);
        
        $message    = str_replace('{prenom}', $prenom, $message);
        
        $message    = str_replace('{logo}', $logo, $message);
        
        //$ref_crypt  = $this->fnEncrypt($details_bag[0]->ref_interne, 'abadingo');
        
        $ref_crypt      = $details_bag[0]->ref_interne;
        
        $url        = 'https://cashdressing.com/index.php?option=com_cashbag&view=cashbag&layout=proposition&key='.$ref_crypt;
        
        $accord     = '<a href ="'.$url.'"> Accepter ou refuser la proposition</a>';
        //var_dump($accord);die();
        $message    = str_replace('{accord}', $accord, $message);
         
        $to      = $mail;
        $subject = 'Proposition d\'achat de cashdressing.com';
        $headers = 'From: contact@cahsdressing.com' . "\r\n" .
                    'Reply-To: contact@cahsdressing.com, kmartin@cashdressing.com' . "\r\n" .
        $headers .= "MIME-Version: 1.0\r\n";
        $headers .= "Content-Type: text/html; charset=utf-8\r\n";//ISO-8859-1
        //return JFactory::
        if( mail($to, $subject, $message, $headers)){//si le mail a bien été envoyé, alors
            
            if($this->locked_proposition($bag_id)){//pose le verrou sur une proposition
                return 'La proposition a bien été envoyé au client';
            }
            else{
                return'erreur demise à jour du publish';
            }
        }    
        
        }
        
        function locked_proposition($bag_id){
            
            $db = &JFactory::getDbo();
            
            $date           = JFactory::getDate();
            $locked         = $date->toFormat('%Y-%m-%d');
            
            $query  = "UPDATE #__vm_cashbag SET date_locked = '".$locked."', published = 2 WHERE id = $bag_id";
            
            $db->setQuery($query);
            //var_dump($query);die();
            return $db->query();
        }
        
       
        
         public function bagdetails(){
            
          $db		= &JFactory::getDbo();
            
            $bag_id     = JFactory::getApplication()->input->get('bag_id');
            
            $query  ="SELECT p.virtuemart_product_id, p.product_type, p.product_state, p.product_cash_price,p.product_cash_comment AS comment, mf.mf_name, cb.vm_user_id "
                    ." FROM #__virtuemart_products AS p"
                    ." JOIN #__virtuemart_product_manufacturers AS m ON p.virtuemart_product_id = m.virtuemart_product_id"
                    ." JOIN #__virtuemart_manufacturers_fr_fr AS mf ON m.virtuemart_manufacturer_id = mf.virtuemart_manufacturer_id"
                    . " JOIN #__vm_cashbag AS cb ON p.cashbag_id = cb.id "
                    . " WHERE p.cashbag_id = $bag_id GROUP BY p.virtuemart_product_id";
            
            $db->setQuery($query);
            $resultat = $db->loadObjectList();
            //var_dump($query);die();
            if(count($resultat)>0){
                $total=0;$nb=0;
                $html = '<table style="width:300px; text-align:center;border:1px #000 solid;"><tr><th width="50px">Type</th><th width="50px">Marque</th><th width="50px">Etat</th></tr>';//
                foreach ($resultat as $r){
                    
                    $total += $r->product_cash_price;
                    $nb++;
                    $html   .="<tr style='text-align:center;'><td> $r->product_type </td><td> $r->mf_name </td><td>".str_replace('_', ' ', $r->product_state)." </td></tr>";//<td>".str_replace('_', ' ', $r->comment)."</td>
                }
                $html   .= '</table>';
               $this->prix_total    = round($total,2);
               $this->nb_articles   = $nb;
            }  else {
                $html   = 'Aucun produit trouvé pour ce CASH BAG';
                $this->prix_total   = 0;
            }
            return $html;
        }
        
	/**
	* Method to build the Order Clause
	*
	* @access private
	* @return string orderby	
	*/
	
	protected function _buildContentOrderBy() 
	{
		$app = &JFactory::getApplication('');
		$context			= $this->option.'.'.strtolower($this->getName()).'.list.';
		$filter_order = $app ->getUserStateFromRequest($context . 'filter_order', 'filter_order', $this->getDefaultFilter(), 'cmd');
		$filter_order_Dir = $app ->getUserStateFromRequest($context . 'filter_order_Dir', 'filter_order_Dir', '', 'word');
		$this->_query->order($filter_order . ' ' . $filter_order_Dir );
	}
	
	/**
	* Method to build the Where Clause 
	*
	* @access private
	* @return string orderby	
	*/
	
	protected function _buildContentWhere() 
	{
		
		$app = &JFactory::getApplication('');
		$context			= $this->option.'.'.strtolower($this->getName()).'.list.';
		
		$filter_order = $app ->getUserStateFromRequest($context . 'filter_order', 'filter_order', $this->getDefaultFilter(), 'cmd');
		$filter_order_Dir = $app ->getUserStateFromRequest($context . 'filter_order_Dir', 'filter_order_Dir', 'desc', 'word');
		$search = $app ->getUserStateFromRequest($context . 'search', 'search', '', 'string');
					
		if ($search) {
			$this->_query->where('LOWER(a.bag_ref) LIKE ' . $this->_db->Quote('%' . $search . '%'));			
		}
		
	}
	
}
?>